# copy from wsgi.py file

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

from employee.models import Student, Score, Students, Scores
from django.db.models import Q, Avg, Sum, Count

Stu = Student.objects.all()
Sco = Score.objects.all()  # Query iterable objects

stumgr = Students.objects
scomgr = Scores.objects

# print(list[Stu])
# print(list[Sco])

# print(Stu.exclude(student_id=1001).filter(pk=1006))
# print(Stu.exclude(student_id=1001).order_by('pk'))
# print(Stu.exclude(student_id=1001).order_by('-pk'))
# print(Stu.exclude(student_id=1001).order_by('-pk').values()) # dict display

# return single value
# print(Stu.filter(pk=1001).first())
# print(Stu.exclude(pk=1001).last())
# print(Stu.filter(pk=1001). get())
# print(Stu.exclude(pk=1001). get()) # get only for 1
# print(Stu.get(pk=1001))
# print(Stu.count())  # int
# print(Stu.exclude(pk=1001).count())

# lookup
# print(Stu.filter(pk__lt=10010).filter(pk__gt=1005))  # and
# print(Stu.filter(pk__in=[1001, 1004, 1006]))  # or in

# and
# print(Stu.filter(pk_no__gt=1001).filter(pk__lt=10015))  # and
# print(Stu.filter(pk__gt=1001, pk__lt=10015))  # and

# Q instance
# print(Stu.filter(Q(pk__gt=1001) & Q(pk__lt=10015)))  # using for and and or
# print(Stu.filter(pk__gt=10010) & Stu.filter(pk__lt=10015))  # 2 queryset using and

# print(Stu.filter(pk__lt=1004) | Stu.filter(pk__gt=1008))  # or
# print(Stu.filter(~Q(pk__lt=1004) | Q(pk__gt=1008)))  # or and then exclude
# print(Stu.filter(Q(pk__lt=1004)) | Q(pk__gt=1008))  # or

# 总成绩大于250分的学生信息 3.2 语文成绩在80-90分之间的学生信息 3.3 有一门科目低于60分的学生信息 3.4 平均分70以上的学生信息 3.5 三门成绩都大于90分的学生姓名

# 3.1 Student total points up to 250
sql = """\
select student_id from (SELECT distinct(student_id),ROUND(SUM( score ), 2) Sum_score
FROM score GROUP BY student_id) where Sum_score>250
"""
stus = stumgr.raw(sql)
# print(type(stus))
print(list(stus))

# 3.2 Student Chinese got 80-90 points
# select * from student where student_id in (select student_id from score where Chinese<90 and Chinese>80);
 stus = (scomgr.filter(course='chinese').filter(score__gt=80, score__lt=90))
 print(stus)
 print(stumgr.filter(student_id__in=stus))

# 3.3 student class failed
# select * from student where student_id in (select student_id from score where chinese<60 or math<60 or english<60);
 stus = scomgr.values('student_id').filter(score__gt=60).distinct()
 print(type(stus))
 print(stus)
 print(stumgr.filter(student_id__in=stus))

# 3.4 Avg Score up to 70
sql = """\
select student_id from (SELECT student_id,ROUND(AVG( score ), 2) Avg_score
FROM score GROUP BY student_id) where Avg_score>70
"""
# stus = stumgr.raw(sql)
# print(type(stus))
# print(list(stus))

# print(stus.filter(student_id__in=[d.get('student_id' for d in stus)]))

# 3.5 all grade Score up to 90
# select * from student where student_id in (select student_id from score where score>90);
 stus = scomgr.values('student_id').filter(score__gt=90).distinct()
 print(stus)
 print(stumgr.filter(student_id__in=stus))

